package com.boot.unity.api.modules.rzgjj.dao;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface GjjDao {
    // 银行卡的有效性查询
    @Select("SELECT * FROM (SELECT g.SA_CUST_CERT_NO AS codeno,g.SA_CUST_NAME AS pername,g.CI_MOBILE_PHONE AS phone,g.SA_CUST_CERT_TYP AS codetype,g.SA_CARD_NO  AS bankcode,g.CI_ACCT_TYP AS accounttype,TO_CHAR(CURRENT DATE ,'YYYYMMDD') AS day_id,'313008' AS banktype,'01' AS rescode,'查询正常' AS retmsg,ROW_NUMBER() OVER(ORDER BY g.SA_CUST_NAME) AS rownum " +
            "FROM GJJYHK g where 1=1 " +
            " and g.SA_CUST_NAME = #{pername} AND g.SA_CUST_CERT_TYP = #{codetype} AND g.SA_CUST_CERT_NO =#{codeno}" +
            " and g.SA_CARD_NO = #{bankcode} ) t WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBAS001Data(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("bankcode") String bankcode,@Param("accounttype") String accounttype,@Param("startNum") int startNum,@Param("endNum") int endNum);

    @Select("SELECT count(1) FROM GJJYHK g where 1=1" +
            " and g.SA_CUST_NAME = #{pername} AND g.SA_CUST_CERT_TYP = #{codetype} AND g.SA_CUST_CERT_NO =#{codeno}" +
            " and g.SA_CARD_NO = #{bankcode}")
    int queryBAS001Count(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("bankcode") String bankcode,@Param("accounttype") String accounttype);






    // 商业银行贷款合同信息
    @Select("select * from (SELECT g.LOANDATE  AS bdate,0 AS ratemny,g.\"limit\" AS loanmnhs,g.BALAMT AS loanbal," +
            "g.LOANACCOUNT AS loancode,g.INTEREST AS loanrate,g.CONTRACTINO AS agrcode,0 AS housearea,0 AS mntpay," +
            "0 AS payday,g.ACCOUNTSTATE AS loanstate,g.CONTRACTMONEY AS loanmny,0 AS fpaymny,0 AS housemny,g.CNAME AS pername," +
            "0 AS basemny,g.ORIENDDATE AS edate,TO_CHAR(CURRENT DATE ,'YYYYMMDD') AS day_id,g.REPAYTYPE AS repayway,g.CUSTIDNO  AS cardcode," +
            "g.CUSTIDTYPE," +
            "ROW_NUMBER() OVER(ORDER BY g.CUSTIDNO) AS rownum FROM sjzt.GJJHT g " +
            " WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}) t " +
            "WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBLS001Data(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno,@Param("startNum") int startNum,@Param("endNum") int endNum);

    @Select("SELECT count(1) FROM sjzt.GJJHT g WHERE g.CUSTIDTYPE = #{codetype} AND g.CNAME = #{pername} and g.CUSTIDNO = #{codeno}")
    int queryBLS001Count(@Param("pername") String pername,@Param("codetype") String codetype,@Param("codeno") String codeno);


    @Select("select count(1) from sjzt.GJJCHJE g,sjzt.GJJYQLX x WHERE g.CUSTIDNO  = #{cardcode} and g.LOANACCOUNT = x.LN_LN_ACCT_NO  AND VARCHAR_FORMAT(x.DATA_DT, 'YYYYMMDD') BETWEEN #{begindate} AND #{enddate}")
    int queryBAS002Count(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate,@Param("enddate") String enddate);

    @Select("SELECT * FROM(SELECT J.CUSTIDNO AS cardcode,j.CNAME AS pername ,j.CUSTIDTYPE AS codetype,j.LOANACCOUNT AS loancode,VARCHAR_FORMAT(CURRENT DATE, 'YYYYMMDD') AS day_id,j.CONTRACTNO AS agrcode,j.YHQS AS payissues, (b.LJCHBJ + y.LN_CRNT_DLAY_INT + x.LJCHLX) AS payedmny,ROW_NUMBER() OVER(ORDER BY j.CNAME) AS rownum FROM sjzt.GJJCHJE j,sjzt.GJJYQLX y,sjzt.GJJLJBJ b,sjzt.GJJLJLX x WHERE  J.CONTRACTNO = b.CONTRACTNO AND j.LOANACCOUNT  = y.LN_LN_ACCT_NO AND j.CONTRACTNO  = x.CONTRACTNO AND J.CUSTIDNO = #{cardcode} AND VARCHAR_FORMAT(y.DATA_DT, 'YYYYMMDD') BETWEEN #{begindate} AND #{enddate}) WHERE rownum BETWEEN #{startNum} AND #{endNum}")
    List<JSONObject> queryBAS002Data(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate, @Param("startNum")int startNum, @Param("endNum")int endNum);


    @Select("SELECT COUNT(1) FROM sjzt.GJJCHJE g,sjzt.GJJBJ j WHERE g.LOANACCOUNT = j.LN_LN_ACCT_NO AND g.CUSTIDNO = #{cardcode} AND VARCHAR_FORMAT(j.DATA_DT, 'YYYYMMDD') BETWEEN #{begindate} AND #{enddate}  ")
    int queryBAS003Count(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate,@Param("enddate") String enddate);

    @Select("SELECT * FROM(SELECT j.CUSTIDNO AS cardcode,j.CNAME AS pername,j.CUSTIDTYPE AS codetype,j.LOANACCOUNT AS loancode,VARCHAR_FORMAT(CURRENT DATE, 'YYYYMM') AS day_id,j.CONTRACTNO AS bkloanacc, h.INTEREST AS loanrate ,g.LN_TNRNO AS mntindx,VARCHAR_FORMAT(g.DATA_DT,'YYYYMM') AS paymnt " +
            ", VARCHAR_FORMAT(g.LN_ENTR_DT ,'YYYY-MM-DD') AS bkdate,g.LN_TX_AMT_PR AS basemny,x.LN_INT AS ratemny,lx.LN_CRNT_DLAY_INT AS moremny,LX.LN_LN_BAL AS loanbal" +
            ",CASE WHEN VARCHAR_FORMAT(g.LN_ENTR_DT, 'DD')  > h.PPRDINTPDAY THEN '逾期还款'" +
            "      WHEN VARCHAR_FORMAT(g.LN_ENTR_DT, 'DD')  < h.PPRDINTPDAY THEN '正常还款'" +
            "    END AS paytype ,ROW_NUMBER() OVER(ORDER BY j.CNAME) AS rownum FROM sjzt.GJJCHJE j,sjzt.GJJHT h ,sjzt.GJJBJ g,sjzt.GJJLX x ,sjzt.GJJYQLX lx WHERE   j.CUSTIDNO = h.CUSTIDNO " +
            "AND J.LOANACCOUNT = g.LN_LN_ACCT_NO AND j.LOANACCOUNT  =  x.LN_LN_ACCT_NO AND g.LN_TNRNO = x.LN_TNRNO  AND g.LN_LN_ACCT_NO  = lx.LN_LN_ACCT_NO  AND g.LN_TNRNO  = lx.LN_TNRNO " +
            "AND j.CUSTIDNO =#{cardcode} AND VARCHAR_FORMAT(g.DATA_DT, 'YYYYMMDD') BETWEEN #{begindate} AND #{enddate})WHERE rownum BETWEEN #{startNum} AND #{endNum} ")
    List<JSONObject> queryBAS003Data(@Param("cardcode")String cardcode,@Param("pername") String pername, @Param("agrcode")String agrcode, @Param("begindate")String begindate, @Param("enddate")String enddate, @Param("startNum")int startNum, @Param("endNum")int endNum);
}
